using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using Lzfy_His_Service.Utilities;
using System.Text;

namespace Lzfy_His_Service.Repositories
{
    /// <summary>
    /// UUM用户仓储实现
    /// </summary>
    public class UumUserRepository : IUumUserRepository
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<UumUserRepository> _logger;

        public UumUserRepository(
            IOracleConnectionFactory oracleConnectionFactory,
            ILogger<UumUserRepository> logger)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
        }



        /// <summary>
        /// 根据用户名查询UUM用户信息
        /// </summary>
        public async Task<UumUser?> GetUserByUserNameAsync(string userName)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();
        try
        {
            using var connection = _oracleConnectionFactory.CreateConnection();
            
            var sql = @"
                SELECT 
                    ID as Id,
                    USER_NAME as UserName,
                    PEOPLE_CLASS as PeopleClass,
                    PEOPLE_IDENTIFIER as PeopleIdentifier,
                    PEOPLE_NAME as PeopleName,
                    WORK_DEPT as WorkDept,
                    WORK_POST as WorkPost,
                    WORK_PROFESSIONAL as WorkProfessional,
                    MOBILE as Mobile,
                    OFFICE_PHONE as OfficePhone,
                    EMAIL as Email,
                    USER_PASSWORD as UserPassword,
                    STATUS as Status,
                    EXPIRES as Expires,
                    DELETE_TIME as DeleteTime,
                    CREATOR as Creator,
                    CREATE_TIME as CreateTime,
                    MODIFIER as Modifier,
                    MODIFY_TIME as ModifyTime,
                    PINYIN_CODE as PinyinCode,
                    WUBI_CODE as WubiCode,
                    DUTY as Duty,
                    ID_NO as IdNo,
                    SUPER_LEADER as SuperLeader,
                    USERPHONE as UserPhone,
                    AUTH_CODE as AuthCode,
                    TASK_AMOUNT as TaskAmount
                FROM UUM.UUM_USER 
                WHERE USER_NAME = :UserName
                  AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                  AND STATUS != 'D'";

            var result = await connection.QuerySingleOrDefaultAsync<UumUser>(sql, new { UserName = userName });
            
            DatabaseExceptionLogger.LogDatabasePerformance(_logger, "GetUumUserByUsername", stopwatch.Elapsed, result != null ? 1 : 0);
            return result;
        }
        catch (Exception ex)
        {
            DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "GetUumUserByUsername", new { userName });
            throw;
        }
    }

        /// <summary>
        /// 根据用户ID查询UUM用户信息
        /// </summary>
        public async Task<UumUser?> GetUserByIdAsync(int userId)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT 
                        ID as Id,
                        USER_NAME as UserName,
                        PEOPLE_CLASS as PeopleClass,
                        PEOPLE_IDENTIFIER as PeopleIdentifier,
                        PEOPLE_NAME as PeopleName,
                        WORK_DEPT as WorkDept,
                        WORK_POST as WorkPost,
                        WORK_PROFESSIONAL as WorkProfessional,
                        MOBILE as Mobile,
                        OFFICE_PHONE as OfficePhone,
                        EMAIL as Email,
                        USER_PASSWORD as UserPassword,
                        STATUS as Status,
                        EXPIRES as Expires,
                        DELETE_TIME as DeleteTime,
                        CREATOR as Creator,
                        CREATE_TIME as CreateTime,
                        MODIFIER as Modifier,
                        MODIFY_TIME as ModifyTime,
                        PINYIN_CODE as PinyinCode,
                        WUBI_CODE as WubiCode,
                        DUTY as Duty,
                        ID_NO as IdNo,
                        SUPER_LEADER as SuperLeader,
                        USERPHONE as UserPhone,
                        AUTH_CODE as AuthCode,
                        TASK_AMOUNT as TaskAmount
                    FROM UUM.UUM_USER 
                    WHERE ID = :UserId
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var result = await connection.QuerySingleOrDefaultAsync<UumUser>(sql, new { UserId = userId });
                
                _logger.LogInformation("查询UUM用户信息: UserId={UserId}, Found={Found}", userId, result != null);
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户信息失败: UserId={UserId}", userId);
                throw;
            }
        }

        /// <summary>
        /// 根据工号查询UUM用户信息
        /// </summary>
        public async Task<UumUser?> GetUserByPeopleIdentifierAsync(string peopleIdentifier)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT 
                        ID as Id,
                        USER_NAME as UserName,
                        PEOPLE_CLASS as PeopleClass,
                        PEOPLE_IDENTIFIER as PeopleIdentifier,
                        PEOPLE_NAME as PeopleName,
                        WORK_DEPT as WorkDept,
                        WORK_POST as WorkPost,
                        WORK_PROFESSIONAL as WorkProfessional,
                        MOBILE as Mobile,
                        OFFICE_PHONE as OfficePhone,
                        EMAIL as Email,
                        USER_PASSWORD as UserPassword,
                        STATUS as Status,
                        EXPIRES as Expires,
                        DELETE_TIME as DeleteTime,
                        CREATOR as Creator,
                        CREATE_TIME as CreateTime,
                        MODIFIER as Modifier,
                        MODIFY_TIME as ModifyTime,
                        PINYIN_CODE as PinyinCode,
                        WUBI_CODE as WubiCode,
                        DUTY as Duty,
                        ID_NO as IdNo,
                        SUPER_LEADER as SuperLeader,
                        USERPHONE as UserPhone,
                        AUTH_CODE as AuthCode,
                        TASK_AMOUNT as TaskAmount
                    FROM UUM.UUM_USER 
                    WHERE PEOPLE_IDENTIFIER = :PeopleIdentifier
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var result = await connection.QuerySingleOrDefaultAsync<UumUser>(sql, new { PeopleIdentifier = peopleIdentifier });
                
                _logger.LogInformation("查询UUM用户信息: PeopleIdentifier={PeopleIdentifier}, Found={Found}", peopleIdentifier, result != null);
                
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户信息失败: PeopleIdentifier={PeopleIdentifier}", peopleIdentifier);
                throw;
            }
        }

        /// <summary>
        /// 检查用户名是否存在
        /// </summary>
        public async Task<bool> UserNameExistsAsync(string userName)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT COUNT(1) 
                    FROM UUM.UUM_USER 
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var count = await connection.QuerySingleAsync<int>(sql, new { UserName = userName });
                
                _logger.LogInformation("检查UUM用户名是否存在: UserName={UserName}, Exists={Exists}", userName, count > 0);
                
                return count > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "检查UUM用户名是否存在失败: UserName={UserName}", userName);
                throw;
            }
        }

        /// <summary>
        /// 根据用户名更新用户电话
        /// </summary>
        public async Task<bool> UpdateUserPhoneAsync(string userName, string? userPhone)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    UPDATE UUM.UUM_USER 
                    SET USERPHONE = :UserPhone,
                        MODIFY_TIME = SYSDATE
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var rowsAffected = await connection.ExecuteAsync(sql, new { UserName = userName, UserPhone = userPhone });
                
                _logger.LogInformation("更新UUM用户电话: UserName={UserName}, UserPhone={UserPhone}, RowsAffected={RowsAffected}", userName, userPhone, rowsAffected);
                
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新UUM用户电话失败: UserName={UserName}, UserPhone={UserPhone}", userName, userPhone);
                throw;
            }
        }

        /// <summary>
        /// 根据用户名查询用户电话
        /// </summary>
        public async Task<string?> GetUserPhoneAsync(string userName)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT USERPHONE 
                    FROM UUM.UUM_USER 
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var userPhone = await connection.QuerySingleOrDefaultAsync<string?>(sql, new { UserName = userName });
                
                _logger.LogInformation("查询UUM用户电话: UserName={UserName}, UserPhone={UserPhone}", userName, userPhone);
                
                return userPhone;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户电话失败: UserName={UserName}", userName);
                throw;
            }
        }

        /// <summary>
        /// 根据用户名更新退费任务数量
        /// </summary>
        public async Task<bool> UpdateTaskAmountAsync(string userName, int taskAmount)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    UPDATE UUM.UUM_USER 
                    SET TASK_AMOUNT = :TaskAmount,
                        MODIFY_TIME = SYSDATE
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var rowsAffected = await connection.ExecuteAsync(sql, new { UserName = userName, TaskAmount = taskAmount });
                
                var success = rowsAffected > 0;
                _logger.LogInformation("更新UUM用户退费任务数量: UserName={UserName}, TaskAmount={TaskAmount}, Success={Success}", userName, taskAmount, success);
                
                return success;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "更新UUM用户退费任务数量失败: UserName={UserName}, TaskAmount={TaskAmount}", userName, taskAmount);
                throw;
            }
        }

        /// <summary>
        /// 根据用户名查询退费任务数量
        /// </summary>
        public async Task<int> GetTaskAmountAsync(string userName)
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    SELECT NVL(TASK_AMOUNT, 0) 
                    FROM UUM.UUM_USER 
                    WHERE USER_NAME = :UserName
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var taskAmount = await connection.QuerySingleOrDefaultAsync<int>(sql, new { UserName = userName });
                
                _logger.LogInformation("查询UUM用户退费任务数量: UserName={UserName}, TaskAmount={TaskAmount}", userName, taskAmount);
                
                return taskAmount;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询UUM用户退费任务数量失败: UserName={UserName}", userName);
                throw;
            }
        }

        /// <summary>
        /// 批量更新授权码：将id_no字段不为空的数据，截取id_no的后四位，加上@再加上user_name，存入auth_code字段
        /// </summary>
        public async Task<(int UpdatedCount, int TotalCount)> BatchUpdateAuthCodeAsync()
        {
            try
            {
                using var connection = _oracleConnectionFactory.CreateConnection();
                
                // 首先查询符合条件的用户数据
                var selectSql = @"
                    SELECT ID, ID_NO, USER_NAME
                    FROM UUM.UUM_USER 
                    WHERE ID_NO IS NOT NULL 
                      AND LENGTH(TRIM(ID_NO)) >= 4
                      AND (DELETE_TIME IS NULL OR DELETE_TIME > SYSDATE)
                      AND STATUS != 'D'";

                var users = await connection.QueryAsync<dynamic>(selectSql);
                var totalCount = users.Count();
                
                if (totalCount == 0)
                {
                    _logger.LogInformation("没有找到需要更新授权码的用户记录");
                    return (0, 0);
                }
                
                var updatedCount = 0;
                
                // 逐个处理用户记录，在服务端进行MD5加密
                foreach (var user in users)
                {
                    var userId = 0;
                    try
                    {
                        var idNo = user.ID_NO?.ToString() ?? "";
                        var userName = user.USER_NAME?.ToString() ?? "";
                        userId = Convert.ToInt32(user.ID);
                        
                        // 在服务端生成加密字符串
                        var plainText = idNo.Length >= 4 ? idNo.Substring(idNo.Length - 4) + "@" + userName : "@" + userName;
                        var authCode = ComputeMD5Hash(plainText);
                        
                        // 更新单个用户的授权码
                        var updateSql = @"
                            UPDATE UUM.UUM_USER 
                            SET AUTH_CODE = :AuthCode,
                                MODIFY_TIME = SYSDATE
                            WHERE ID = :UserId";
                        
                        var result = await connection.ExecuteAsync(updateSql, new { AuthCode = authCode, UserId = userId });
                        if (result > 0)
                        {
                            updatedCount++;
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogWarning(ex, "更新用户ID {UserId} 的授权码失败", userId);
                    }
                }
                
                _logger.LogInformation("批量更新UUM用户授权码完成: TotalCount={TotalCount}, UpdatedCount={UpdatedCount}", totalCount, updatedCount);
                
                return (updatedCount, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "批量更新UUM用户授权码失败");
                throw;
            }
        }
        
        /// <summary>
        /// 计算MD5哈希值
        /// </summary>
        private string ComputeMD5Hash(string input)
        {
            using var md5 = System.Security.Cryptography.MD5.Create();
            var inputBytes = System.Text.Encoding.UTF8.GetBytes(input);
            var hashBytes = md5.ComputeHash(inputBytes);
            return Convert.ToHexString(hashBytes).ToLower();
        }
    }
}